<?php
/**
 * 分表查询
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2020/12/25
 * Time: 15:17
 */

namespace App\Http\Controllers\Home;

use App\Http\Controllers\Controller;
use App\Models\Logs;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

class FenbiaoController extends Controller
{
    public function __construct()
    {
        //Logs::first();
    }

    public function index()
    {
        //$uid = 1;
        //$data = Logs::where('user_id',$uid)->first();
        $data = [];
        for ($i=0;$i<9;$i++){
            $data[$i]['user_id'] = 1;
            $data[$i]['username'] = '张三';
            $data[$i]['content'] = "内容";
            $data[$i]['created_at'] = date('Y-m-d H:i:s',time());
        }
        $logsObj = new Logs();
        //$logsObj->addAll($data);


        //$res = Logs::create($ini);
        //$data = Logs::get()->toArray();

        $endTime = date('Y-m-d H:i:s', time());
        $start = Carbon::parse(date("Y-m-01 00:00:00", strtotime("-1 month")));
        $end = Carbon::parse($endTime);
        // 查询集合
        $queries = collect();
        $queries->push(
            DB::table("logs")
                // 建议都用select查询字段，SQL尽可能的优化性能
                ->select('id', 'user_id', 'username', 'content', 'created_at')
//                ->orderBy('id', 'asc')
//                ->limit(99999999)
        );
        // 循环比较年月，添加每一张表的查询
        for ($i = $start->copy(); $i->format('Ym') <= $end->format('Ym'); $i->addMonth()) {
            $queries->push(
                DB::table("logs_{$i->format('Ym')}")
                    // 建议都用select查询字段，SQL尽可能的优化性能
                    ->select('id', 'user_id', 'username', 'content', 'created_at')
//                    ->orderBy('id', 'desc')
//                    ->limit(99999999)
            );
        }
        // 出列一张表作为union的开始
        $unionQuery = $queries->shift();
        // 循环剩下的表添加union
        $queries->each(function ($item, $key) use ($unionQuery) {
            $unionQuery->unionAll($item);
        });
        // 设置临时表的名称，添加临时表，顺序不能反过来，否则用关联约束会找不到表
        $lists = with(new Logs)->setTable('union_logs')
            // 添加临时表
            ->from(DB::raw("({$unionQuery->toSql()}) as union_logs"))
            // 合并查询条件
            ->mergeBindings($unionQuery)
            // 关联约束，不在用户表的不用查出来
            // 分页
            ->paginate(10)->toArray();
        echo "<pre>";
        print_r($lists);
        echo "</pre>";
        exit();


    }

}